import xlrd
from lxml import etree
import os
import PySimpleGUI as sg

print('开始启动图形界面')
#图形界面
sg.theme('Dark Blue 3')  # please make your windows colorful

layout = [
            [sg.Text('清单表'),sg.Input(), sg.FileBrowse()],
          
          [sg.Text('inp配置文件表'),sg.Input(), sg.FileBrowse()],
           
          
          [sg.Text('inpTXT文件夹'), sg.InputText(), sg.FolderBrowse()],
            [sg.Text('任务单'), sg.InputText(), sg.FolderBrowse()],
           [sg.OK(), sg.Cancel()] ,
          ]

window = sg.Window('回复生成', layout)
event, values = window.read()
window.close()

config,inpfile,inpfolder,order=values[0],values[1],values[2]+'/',values[3]+'/'



print('从配置config.xls文件里,读取起止ICCID写入字典，输入任意ICCID，返回文件名')
#从config文件里读取文件名，起止ICCID
excel0 = xlrd.open_workbook(config)  # 打开excel文件
sheet0 = excel0.sheet_by_index(0)
nrows0=sheet0.nrows 
#把数据写到字典中，键是文件名，值是起止ICCID
dict0={}
for i in range(1,nrows0):
    dict0[sheet0.cell_value(i,2)]=[int(sheet0.cell_value(i,6)[:-1]),int(sheet0.cell_value(i,7)[:-1])]

#查询ICCID,输入ICCID，返回文件名
def query_ICCID(x):
    for key in dict0.keys():
        if x >=dict0[key][0] and x <=dict0[key][1]:
            return key

print('找出订单和des文件的对应关系，键为des文件名，值为订单号')
#找出订单和des文件的对应关系，键为des文件名，值为订单号
order_folder=order
dict1={}
for file in os.listdir(order_folder):
    xlsfilename=order_folder+file
    with open(xlsfilename,'r') as f:
        x=f.readline()
    #print(x)
    demo=etree.HTML(x)
    #print(demo)
    t=etree.tostring(demo,encoding="utf-8",pretty_print=True)
    list0=demo.xpath('//*')
    for item in list0:
        if str(item.text).endswith('des'):
            dict1[item.text[:-4]]=file[:-4]

print('打开每一个inp的txt文件，查询每一个ICCID,返回文件名和订单号')
#打开inp文件找到对应的inp的txt文件，检测每一个ICCID
excel1 = xlrd.open_workbook(inpfile)  # 打开excel文件
sheet1 = excel1.sheet_by_index(0)
nrows1=sheet1.nrows
if os.path.exists('result.csv'):
    os.remove('result.csv')
#print(nrows)
for i in range(0,nrows1):
    list1=[]
    if i==0:
        pass
    else:
        inp_filename=inpfolder+sheet1.cell_value(i,2)[:-4]+'.txt'
        #print(inp_filename)
        with open(inp_filename,'r') as f:
            x=f.readlines()
        filename=''
        #list1=[]
        for line in x:
            tmp=query_ICCID(int(line[:19]))
            if tmp==filename:
                pass
            else:
                
                filename=tmp
                list1.append(filename+','+dict1[filename])
    #print(list1)
    oldlist=[sheet1.cell_value(i,0),sheet1.cell_value(i,1),sheet1.cell_value(i,2),'\''+str(sheet1.cell_value(i,3)),\
          '\''+str(sheet1.cell_value(i,4)),sheet1.cell_value(i,5),sheet1.cell_value(i,6)]
    with open('result.csv','a') as f:
        f.write(','.join(oldlist)+','+','.join(list1)+'\n')
print('开始打开生成的csv文件')
os.system('start result.csv')
print('end')

